--    Program: 		CAPARIMP_PAYMENT_SCHEDULE.sql
--    Description:  Import AR Payment Schedule information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         2-Feb-2006

set doc off      
set verify off
set serveroutput on size 1000000
--whenever sqlerror exit failure rollback

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_payment_schedule_id	number;
   lv_counter				VARCHAR(100);
   
   	lv_FileName		VARCHAR2(100)	:=	'log.log';
	lv_PathName		VARCHAR2(100)	:=	'/tmp';
	lv_UTL_File		UTL_FILE.File_Type;
                    		                 		
   cursor cur_payment_schedule is
      select * from CRST_AR_PAYMENT_SCHEDULES_ALL
      --WHERE PAYMENT_SCHEDULE_ID = 28750
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   lv_counter := 0;

   
   for cur in cur_payment_schedule loop   	
     l_err_msg_tmp := '';
     lv_counter := lv_counter + 1;
     
     lv_UTL_File := UTL_FILE.FOpen(lv_PathName, lv_FileName, 'w');
     UTL_FILE.PUT_LINE(lv_UTL_File, lv_counter);
	 UTL_FILE.FClose(lv_UTL_File);
     
     SELECT AR_PAYMENT_SCHEDULES_S.NEXTVAL 
     INTO l_payment_schedule_id 
     FROM DUAL;
     
     update CRST_AR_PAYMENT_SCHEDULES_ALL
     set    payment_schedule_id_n = l_payment_schedule_id 
     where  payment_schedule_id = cur.payment_schedule_id;
     
     begin
       -- import data
       insert into AR_PAYMENT_SCHEDULES_ALL
       (
         PAYMENT_SCHEDULE_ID             ,
         STAGED_DUNNING_LEVEL            ,
         DUNNING_LEVEL_OVERRIDE_DATE     ,
         LAST_UPDATE_DATE                ,
         LAST_UPDATED_BY                 ,
         CREATION_DATE                   ,
         CREATED_BY                      ,
         LAST_UPDATE_LOGIN               ,
         DUE_DATE                        ,
         AMOUNT_DUE_ORIGINAL             ,
         AMOUNT_DUE_REMAINING            ,
         NUMBER_OF_DUE_DATES             ,
         STATUS                          ,
         INVOICE_CURRENCY_CODE           ,
         CLASS                           ,
         CUST_TRX_TYPE_ID                ,
         CUSTOMER_ID                     ,
         CUSTOMER_SITE_USE_ID            ,
         CUSTOMER_TRX_ID                 ,
         CASH_RECEIPT_ID                 ,
         ASSOCIATED_CASH_RECEIPT_ID      ,
         TERM_ID                         ,
         TERMS_SEQUENCE_NUMBER           ,
         GL_DATE_CLOSED                  ,
         ACTUAL_DATE_CLOSED              ,
         DISCOUNT_DATE                   ,
         AMOUNT_LINE_ITEMS_ORIGINAL      ,
         AMOUNT_LINE_ITEMS_REMAINING     ,
         AMOUNT_APPLIED                  ,
         AMOUNT_ADJUSTED                 ,
         AMOUNT_IN_DISPUTE               ,
         AMOUNT_CREDITED                 ,
         RECEIVABLES_CHARGES_CHARGED     ,
         RECEIVABLES_CHARGES_REMAINING   ,
         FREIGHT_ORIGINAL                ,
         FREIGHT_REMAINING               ,
         TAX_ORIGINAL                    ,
         TAX_REMAINING                   ,
         DISCOUNT_ORIGINAL               ,
         DISCOUNT_REMAINING              ,
         DISCOUNT_TAKEN_EARNED           ,
         DISCOUNT_TAKEN_UNEARNED         ,
         IN_COLLECTION                   ,
         CASH_APPLIED_ID_LAST            ,
         CASH_APPLIED_DATE_LAST          ,
         CASH_APPLIED_AMOUNT_LAST        ,
         CASH_APPLIED_STATUS_LAST        ,
         CASH_GL_DATE_LAST               ,
         CASH_RECEIPT_ID_LAST            ,
         CASH_RECEIPT_DATE_LAST          ,
         CASH_RECEIPT_AMOUNT_LAST        ,
         CASH_RECEIPT_STATUS_LAST        ,
         EXCHANGE_RATE_TYPE              ,
         EXCHANGE_DATE                   ,
         EXCHANGE_RATE                   ,
         ADJUSTMENT_ID_LAST              ,
         ADJUSTMENT_DATE_LAST            ,
         ADJUSTMENT_GL_DATE_LAST         ,
         ADJUSTMENT_AMOUNT_LAST          ,
         FOLLOW_UP_DATE_LAST             ,
         FOLLOW_UP_CODE_LAST             ,
         PROMISE_DATE_LAST               ,
         PROMISE_AMOUNT_LAST             ,
         COLLECTOR_LAST                  ,
         CALL_DATE_LAST                  ,
         TRX_NUMBER                      ,
         TRX_DATE                        ,
         ATTRIBUTE_CATEGORY              ,
         ATTRIBUTE1                      ,
         ATTRIBUTE2                      ,
         ATTRIBUTE3                      ,
         ATTRIBUTE4                      ,
         ATTRIBUTE5                      ,
         ATTRIBUTE6                      ,
         ATTRIBUTE7                      ,
         ATTRIBUTE8                      ,
         ATTRIBUTE9                      ,
         ATTRIBUTE10                     ,
         REVERSED_CASH_RECEIPT_ID        ,
         AMOUNT_ADJUSTED_PENDING         ,
         ATTRIBUTE11                     ,
         ATTRIBUTE12                     ,
         ATTRIBUTE13                     ,
         ATTRIBUTE14                     ,
         ATTRIBUTE15                     ,
         GL_DATE                         ,
         ACCTD_AMOUNT_DUE_REMAINING      ,
         PROGRAM_APPLICATION_ID          ,
         PROGRAM_ID                      ,
         PROGRAM_UPDATE_DATE             ,
         RECEIPT_CONFIRMED_FLAG          ,
         REQUEST_ID                      ,
         SELECTED_FOR_RECEIPT_BATCH_ID   ,
         LAST_CHARGE_DATE                ,
         SECOND_LAST_CHARGE_DATE         ,
         DISPUTE_DATE                    ,
         ORG_ID                          ,
         GLOBAL_ATTRIBUTE1               ,
         GLOBAL_ATTRIBUTE2               ,
         GLOBAL_ATTRIBUTE3               ,
         GLOBAL_ATTRIBUTE4               ,
         GLOBAL_ATTRIBUTE5               ,
         GLOBAL_ATTRIBUTE6               ,
         GLOBAL_ATTRIBUTE7               ,
         GLOBAL_ATTRIBUTE8               ,
         GLOBAL_ATTRIBUTE9               ,
         GLOBAL_ATTRIBUTE10              ,
         GLOBAL_ATTRIBUTE11              ,
         GLOBAL_ATTRIBUTE12              ,
         GLOBAL_ATTRIBUTE13              ,
         GLOBAL_ATTRIBUTE14              ,
         GLOBAL_ATTRIBUTE15              ,
         GLOBAL_ATTRIBUTE16              ,
         GLOBAL_ATTRIBUTE17              ,
         GLOBAL_ATTRIBUTE18              ,
         GLOBAL_ATTRIBUTE19              ,
         GLOBAL_ATTRIBUTE20              ,
         GLOBAL_ATTRIBUTE_CATEGORY       ,
         CONS_INV_ID                     ,
         CONS_INV_ID_REV                 ,
         EXCLUDE_FROM_DUNNING_FLAG       ,
         MRC_CUSTOMER_TRX_ID             ,
         MRC_EXCHANGE_RATE_TYPE          ,
         MRC_EXCHANGE_DATE               ,
         MRC_EXCHANGE_RATE               ,
         MRC_ACCTD_AMOUNT_DUE_REMAINING  ,
         BR_AMOUNT_ASSIGNED              ,
         RESERVED_TYPE                   ,
         RESERVED_VALUE                
       )
       values
       (
          l_payment_schedule_id               ,
          cur.STAGED_DUNNING_LEVEL            ,
          cur.DUNNING_LEVEL_OVERRIDE_DATE     ,
          cur.LAST_UPDATE_DATE                ,
          CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY), 
          cur.CREATION_DATE                   ,
          CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
          cur.LAST_UPDATE_LOGIN               ,
          cur.DUE_DATE                        ,
          cur.AMOUNT_DUE_ORIGINAL             ,
          cur.AMOUNT_DUE_REMAINING            ,
          cur.NUMBER_OF_DUE_DATES             ,
          cur.STATUS                          ,
          cur.INVOICE_CURRENCY_CODE           ,
          cur.CLASS                           ,
          CAPARIMP_MAPPING.GetTransactionTypeIDMapping(cur.ORG_ID, cur.CUST_TRX_TYPE_ID),
          CAPARIMP_MAPPING.GetHZCustAccountIDMapping(cur.CUSTOMER_ID),
          CAPARIMP_MAPPING.GetHZSiteUseIDMapping(cur.CUSTOMER_SITE_USE_ID),
          CAPARIMP_MAPPING.GetCustomerTrxIDMapping(cur.CUSTOMER_TRX_ID),
          CAPARIMP_MAPPING.GetCashReceiptIDMapping(cur.CASH_RECEIPT_ID),
          CAPARIMP_MAPPING.GetCashReceiptIDMapping(cur.ASSOCIATED_CASH_RECEIPT_ID),
          cur.TERM_ID                         ,
          cur.TERMS_SEQUENCE_NUMBER           ,
          cur.GL_DATE_CLOSED                  ,
          cur.ACTUAL_DATE_CLOSED              ,
          cur.DISCOUNT_DATE                   ,
          cur.AMOUNT_LINE_ITEMS_ORIGINAL      ,
          cur.AMOUNT_LINE_ITEMS_REMAINING     ,
          cur.AMOUNT_APPLIED                  ,
          cur.AMOUNT_ADJUSTED                 ,
          cur.AMOUNT_IN_DISPUTE               ,
          cur.AMOUNT_CREDITED                 ,
          cur.RECEIVABLES_CHARGES_CHARGED     ,
          cur.RECEIVABLES_CHARGES_REMAINING   ,
          cur.FREIGHT_ORIGINAL                ,
          cur.FREIGHT_REMAINING               ,
          cur.TAX_ORIGINAL                    ,
          cur.TAX_REMAINING                   ,
          cur.DISCOUNT_ORIGINAL               ,
          cur.DISCOUNT_REMAINING              ,
          cur.DISCOUNT_TAKEN_EARNED           ,
          cur.DISCOUNT_TAKEN_UNEARNED         ,
          cur.IN_COLLECTION                   ,
          cur.CASH_APPLIED_ID_LAST            ,
          cur.CASH_APPLIED_DATE_LAST          ,
          cur.CASH_APPLIED_AMOUNT_LAST        ,
          cur.CASH_APPLIED_STATUS_LAST        ,
          cur.CASH_GL_DATE_LAST               ,
          cur.CASH_RECEIPT_ID_LAST            ,
          cur.CASH_RECEIPT_DATE_LAST          ,
          cur.CASH_RECEIPT_AMOUNT_LAST        ,
          cur.CASH_RECEIPT_STATUS_LAST        ,
          cur.EXCHANGE_RATE_TYPE              ,
          cur.EXCHANGE_DATE                   ,
          cur.EXCHANGE_RATE                   ,
          cur.ADJUSTMENT_ID_LAST              ,
          cur.ADJUSTMENT_DATE_LAST            ,
          cur.ADJUSTMENT_GL_DATE_LAST         ,
          cur.ADJUSTMENT_AMOUNT_LAST          ,
          cur.FOLLOW_UP_DATE_LAST             ,
          cur.FOLLOW_UP_CODE_LAST             ,
          cur.PROMISE_DATE_LAST               ,
          cur.PROMISE_AMOUNT_LAST             ,
          cur.COLLECTOR_LAST                  ,
          cur.CALL_DATE_LAST                  ,
          cur.TRX_NUMBER                      ,
          cur.TRX_DATE                        ,
          cur.ATTRIBUTE_CATEGORY              ,
          cur.ATTRIBUTE1                      ,
          cur.ATTRIBUTE2                      ,
          cur.ATTRIBUTE3                      ,
          cur.ATTRIBUTE4                      ,
          cur.ATTRIBUTE5                      ,
          cur.ATTRIBUTE6                      ,
          cur.ATTRIBUTE7                      ,
          cur.ATTRIBUTE8                      ,
          cur.ATTRIBUTE9                      ,
          cur.ATTRIBUTE10                     ,
          cur.REVERSED_CASH_RECEIPT_ID        ,
          cur.AMOUNT_ADJUSTED_PENDING         ,
          cur.ATTRIBUTE11                     ,
          cur.ATTRIBUTE12                     ,
          cur.ATTRIBUTE13                     ,
          cur.ATTRIBUTE14                     ,
          cur.ATTRIBUTE15                     ,
          cur.GL_DATE                         ,
          cur.ACCTD_AMOUNT_DUE_REMAINING      ,
          cur.PROGRAM_APPLICATION_ID          ,
          cur.PROGRAM_ID                      ,
          cur.PROGRAM_UPDATE_DATE             ,
          cur.RECEIPT_CONFIRMED_FLAG          ,
          cur.REQUEST_ID                      ,
          cur.SELECTED_FOR_RECEIPT_BATCH_ID   ,
          cur.LAST_CHARGE_DATE                ,
          cur.SECOND_LAST_CHARGE_DATE         ,
          cur.DISPUTE_DATE                    ,
          CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID),
          cur.GLOBAL_ATTRIBUTE1               ,
          cur.GLOBAL_ATTRIBUTE2               ,
          cur.GLOBAL_ATTRIBUTE3               ,
          cur.GLOBAL_ATTRIBUTE4               ,
          cur.GLOBAL_ATTRIBUTE5               ,
          cur.GLOBAL_ATTRIBUTE6               ,
          cur.GLOBAL_ATTRIBUTE7               ,
          cur.GLOBAL_ATTRIBUTE8               ,
          cur.GLOBAL_ATTRIBUTE9               ,
          cur.GLOBAL_ATTRIBUTE10              ,
          cur.GLOBAL_ATTRIBUTE11              ,
          cur.GLOBAL_ATTRIBUTE12              ,
          cur.GLOBAL_ATTRIBUTE13              ,
          cur.GLOBAL_ATTRIBUTE14              ,
          cur.GLOBAL_ATTRIBUTE15              ,
          cur.GLOBAL_ATTRIBUTE16              ,
          cur.GLOBAL_ATTRIBUTE17              ,
          cur.GLOBAL_ATTRIBUTE18              ,
          cur.GLOBAL_ATTRIBUTE19              ,
          cur.GLOBAL_ATTRIBUTE20              ,
          cur.GLOBAL_ATTRIBUTE_CATEGORY       ,
          cur.CONS_INV_ID                     ,
          cur.CONS_INV_ID_REV                 ,
          cur.EXCLUDE_FROM_DUNNING_FLAG       ,
          cur.MRC_CUSTOMER_TRX_ID             ,
          cur.MRC_EXCHANGE_RATE_TYPE          ,
          cur.MRC_EXCHANGE_DATE               ,
          cur.MRC_EXCHANGE_RATE               ,
          cur.MRC_ACCTD_AMOUNT_DUE_REMAINING  ,
          cur.BR_AMOUNT_ASSIGNED              ,
          cur.RESERVED_TYPE                   ,
          cur.RESERVED_VALUE                                             
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp = '' then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   
   if l_rejected_count <> 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPARIMP_PAYMENT_SCHEDULE', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
